﻿CREATE PROCEDURE [dbo].[GetNearByPropertySaleRent]
@Mode nvarchar(5),
@Lat decimal(9,6) = 5.414921,
@Lng decimal(9,6) = 100.3297610,
@Distant int = 5,
@offset int = 0,
@next int = 20
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
DECLARE @DistAppr decimal(9,6);
SET @DistAppr = @Distant * 0.008;
DECLARE @PriceMax bigint;
SET @PriceMax = 0;
DECLARE @PriceMin bigint; 
SET @PriceMin= 0;
DECLARE @Bedroom int; SET @Bedroom= 0;
DECLARE @FloorSize int; SET @FloorSize= 0;
DECLARE @Location nvarchar(30); SET @Location= '';
DECLARE @BestBuy int; SET  @BestBuy= 2;

set @BestBuy = 1 - @BestBuy;

IF @Mode='Rent'

(select 'RR' as modetype,a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.SmallUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.FloorLoad,a.Ceiling,a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,
case 
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0) 
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0) 
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0) 
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0) 
else 99999
end
as RentPrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.RentRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.rlisterid = f.staffid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_picture g on a.propertyid = g.propertyid
where a.rentview = 1 
and f.statusdelete = 0
and (a.RRYes = 1)
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr 
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc, a.RentRemark 

)UNION ALL(

select 'RL' as modetype,a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.SmallUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,
case 
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0) 
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0) 
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0) 
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0) 
else 99999
end
as RentPrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.RentRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.rlisterid = f.staffid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_picture g on a.propertyid = g.propertyid
where a.rentview = 1 
and f.statusdelete = 0
and (a.RLYes=1)
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr 
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.RentRemark 
) UNION ALL (
select 'RI' as modetype,a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.SmallUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.FloorLoad,a.Ceiling,a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,
case 
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0) 
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0) 
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0) 
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0) 
else 99999
end
as RentPrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.RentRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.rlisterid = f.staffid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_picture g on a.propertyid = g.propertyid
where a.rentview = 1 
and f.statusdelete = 0
and (a.RIYes=1)
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr 
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl, a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.RentRemark 
) UNION ALL (
--ELSE IF @Mode = 'RC'
select 'RC' as modetype,a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.SmallUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
 a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,
case 
when ISNULL(a.RRAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RRAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RRAmt,0) 
when ISNULL(a.RCAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RIAmt,0) and ISNULL(a.RCAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RCAmt,0) 
when ISNULL(a.RIAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RIAmt,0) >= ISNULL(a.RLAmt,0) then ISNULL(a.RIAmt,0) 
when ISNULL(a.RLAmt,0) >= ISNULL(a.RRAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RCAmt,0) and ISNULL(a.RLAmt,0) >= ISNULL(a.RIAmt,0) then ISNULL(a.RLAmt,0) 
else 99999
end
as RentPrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.RentRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.rlisterid = f.staffid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_picture g on a.propertyid = g.propertyid
where a.rentview = 1 
and f.statusdelete = 0
and (a.RCYes=1)
and a.RRelistStatus = 0 and a.AgentFeedbackRent is null
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr 
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl , a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.RRAmt,a.RCAmt,a.RIAmt,a.RLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.RentRemark 
)order by a.UpdateDate desc,a.CreateDate desc

ELSE IF @Mode='Sale'

(select 'SR' as modetype,a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.SmallUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy, 
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
 a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.SaleRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.slisterid = f.staffid
left join aw_picture g on a.propertyid = g.propertyid
where a.saleview = 1 
and f.statusdelete = 0
and (a.SRYes = 1)
and a.SRelistStatus = 0 and a.AgentFeedback is null
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr  
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl , a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.SaleRemark 
)UNION ALL(
--ELSE IF @Mode = 'SL'
select 'SL' as modetype,a.propertyid,a.Latitude,a.Longitude,  b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.SmallUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
 a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.SaleRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.slisterid = f.staffid
left join aw_picture g on a.propertyid = g.propertyid
where a.saleview = 1 
and f.statusdelete = 0
and (a.SLYes=1)
and a.SRelistStatus = 0 and a.AgentFeedback is null
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr 
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl , a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.SaleRemark 
)UNION ALL(
--ELSE IF @Mode = 'SI'
select 'SI' as modetype,a.propertyid,a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.SmallUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.SaleRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.SListerId = f.staffid
left join aw_picture g on a.propertyid = g.propertyid
where a.saleview = 1 
and f.statusdelete = 0
and (a.SIYes=1)
and a.SRelistStatus = 0 and a.AgentFeedback is null
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr 
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl , a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate, e.TypeDesc,a.SaleRemark 
)UNION ALL(
--ELSE IF @Mode = 'SC'
select 'SC' as modetype,a.propertyid,a.Latitude,a.Longitude,  b.road,c.location, d.state,e.type,f.staffid,f.name as agentname, f.mobile,f.email,f.SmallUrl as StaffPicURL, a.LASF as LandSqrtFt, a.BUSF as BuildUp,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom,a.FloorLoad,a.Ceiling, a.BestBuy,
case 
when a.Tenure = 1 then 'Freehold'
when a.Tenure = 2 then 'Leasehold'
else '' end
as Tenure,
case
when a.Facing = 1 then 'North'
when a.Facing = 2 then 'North East'
when a.Facing = 3 then 'East'
when a.Facing = 4 then 'South East'
when a.Facing = 5 then 'South'
when a.Facing = 6 then 'South West'
when a.Facing = 7 then 'West'
when a.Facing = 8 then 'North West'
when a.Facing = 9 then 'N/A'
else '' end as Facing,
case
when a.Zoning = 1 then 'Commercial'
when a.Zoning = 2 then 'Residential'
when a.Zoning = 3 then 'Transition'
when a.Zoning = 4 then 'Industrial'
when a.Zoning = 5 then 'Algriculture'
when a.Zoning = 6 then 'Others'
else '' end as Zoning,
case
when a.Furnished = 1 then 'Furnished'
when a.Furnished = 2 then 'Not Furnished'
when a.Furnished = 3 then 'Partially Furnished'
else '' end as Furnished,
a.FurnishedRemark,
case
when a.Renovated = 1 then 'Renovated'
when a.Renovated = 2 then 'Not Renovated'
when a.Renovated = 3 then 'Partially Renovated'
else '' end as Renovated,
a.RenovatedRemark,
''  as Condition,
a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,
case 
when ISNULL(a.SRAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SRAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SRAmt,0) 
when ISNULL(a.SCAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SIAmt,0) and ISNULL(a.SCAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SCAmt,0) 
when ISNULL(a.SIAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SIAmt,0) >= ISNULL(a.SLAmt,0) then ISNULL(a.SIAmt,0) 
when ISNULL(a.SLAmt,0) >= ISNULL(a.SRAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SCAmt,0) and ISNULL(a.SLAmt,0) >= ISNULL(a.SIAmt,0) then ISNULL(a.SLAmt,0) 
else 99999999
end
as SalePrice,a.UpdateDate, a.CreateDate,
case when a.UpdateDate is null then a.CreateDate 
else a.UpdateDate end as UpdatedDate,max(g.SmallUrl) as PropertyURL,
isnull(e.TypeDesc,'') +Isnull(', '+ h.Project,'') as LongHeading, 
isnull(e.type,'') +Isnull(', '+ h.Project,'') as ShortHeading,
a.SaleRemark as Remark
from aw_property a
left join aw_road b on a.roadid = b.roadid
left join AW_Project h on a.ProjectId = h.ProjectId
left join aw_location c on b.locationid = c.locationid
left join aw_state d on c.stateid = d.stateid
left join aw_propertytype e on a.propertytypeid = e.typeid
left join aw_staff f on a.SListerId = f.staffid
left join aw_picture g on a.propertyid = g.propertyid
where a.saleview = 1 
and f.statusdelete = 0
and (a.SCYes=1)
and a.SRelistStatus = 0 and a.AgentFeedback is null
and abs(a.Latitude - @Lat)<@DistAppr AND abs(a.Longitude - @Lng)<@DistAppr 
group by a.propertyid, a.Latitude,a.Longitude, b.road,c.location, d.state,e.type,f.staffid,f.name, f.mobile,f.email,f.SmallUrl , a.LASF, a.BUSF,h.Project,
a.ProjectRemark, a.Developer,a.PropertyDesc, a.Room, a.Bathroom, a.BestBuy,a.Tenure,a.Facing,a.Zoning,a.Furnished,a.FurnishedRemark
,a.Renovated,a.RenovatedRemark,a.FloorLoad,a.Ceiling,
a.Condition, a.SRAmt,a.SCAmt,a.SIAmt,a.SLAmt,a.UpdateDate,a.CreateDate,e.TypeDesc,a.SaleRemark 
)order by a.UpdateDate desc,a.CreateDate desc

ELSE
select 'WRONG INPUT PARAMETER(Rent/Sale)' AS NORESULT from Mobile_Settings
END

